﻿using EntitiesTM;
using System.Data.SqlClient;
using System.Data;
using Utilities;

namespace DataAccess
{
    public class DaStaff
    {
        public DaStaff()
        {

        }

        public int checkSignIn(string email, string password)
        {
            string strCommandText = "checkSignIn";
            SqlParameter[] paraLocal = new SqlParameter[3];
            paraLocal[0] = new SqlParameter("@Email", email);
            paraLocal[1] = new SqlParameter("@Password", password);
            paraLocal[2] = new SqlParameter("@Result", SqlDbType.SmallInt);
            paraLocal[2].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);

            int intResult = int.Parse(paraLocal[2].Value.ToString());

            return intResult;
        }
        public int[] CheckStaffExists(EnStaff staff)
        {
            string strCommandText = "checkLogin";
            SqlParameter[] paraLocal = new SqlParameter[4];
            paraLocal[0] = new SqlParameter("@Email", staff.staffEmail);
            paraLocal[1] = new SqlParameter("@Password", staff.staffPassword);
            paraLocal[2] = new SqlParameter("@Result", SqlDbType.SmallInt);
            paraLocal[3] = new SqlParameter("@CompanyID", SqlDbType.Int);
            paraLocal[2].Direction = ParameterDirection.Output;
            paraLocal[3].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
            
            int[] intResult = {int.Parse(paraLocal[2].Value.ToString()),int.Parse(paraLocal[3].Value.ToString())};

            return intResult;
        }

        public int[] checkEmail(string email)
        {
            string strCommandText = "checkEmail";
            SqlParameter[] paraLocal = new SqlParameter[3];
            paraLocal[0] = new SqlParameter("@Email", email);
            paraLocal[1] = new SqlParameter("@Result", SqlDbType.SmallInt);
            paraLocal[2] = new SqlParameter("@CompanyID", SqlDbType.Int);
            paraLocal[1].Direction = ParameterDirection.Output;
            paraLocal[2].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
            int[] intResult = {int.Parse(paraLocal[1].Value.ToString()),int.Parse(paraLocal[2].Value.ToString())};

            return intResult;
        }

        public DataTable selStaffByEmail(string email, string companyid)
        {
            string strCommandText = "selStaffByEmailTmp" + companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@Email", email);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public DataTable selAllStaff(string companyid)
        {
            string strCommandText = "selAllStaffTmp" + companyid;
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, null);
        }

        public DataTable selAllStaffOpt(string companyid)
        {
            string strCommandText = "selAllStaffOpt" + companyid;
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, null);
        }

        public DataTable selAllStaffOptByResultUnit(string email, string companyid)
        {
            string strCommandText = "selAllStaffOptByResultUnit" + companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@Email", email);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public DataTable GetStaffByEmailOpt(string email,string companyid)
        {
            string strCommandText = "selStaffByEmailOpt" + companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@Email", email);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }
        public DataTable selStaffIDByEmail(string email, string companyid)
        {
            string strCommandText = "selStaffIDByEmail" + companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@Email", email);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }
        public DataTable selResultUnitIDByEmail(string email, string companyid)
        {
            string strCommandText = "selResultUnitIDByEmail" + companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@Email", email);
            return SqlHelper.ExecuteData(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void updStaffOpt(EnStaff staff, string companyid)
        {
            string strCommandText = "updStaffOptTmp" + companyid;
            SqlParameter[] paraLocal = new SqlParameter[5];
            paraLocal[0] = new SqlParameter("@StaffName", staff.staffName);
            paraLocal[1] = new SqlParameter("@StaffEmail", staff.staffEmail);
            paraLocal[2] = new SqlParameter("@StaffPosition", staff.staffPosition);
            paraLocal[3] = new SqlParameter("@StaffResultUnitID", staff.staffResultUnitID);
            paraLocal[4] = new SqlParameter("@StaffPermissionID", staff.staffPermissionID);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void resetPass(EnStaff staff, string companyid)
        {
            string strCommandText = "resetPass" + companyid;
            SqlParameter[] paraLocal = new SqlParameter[2];
            paraLocal[0] = new SqlParameter("@Pass", staff.staffPassword);
            paraLocal[1] = new SqlParameter("@Email", staff.staffEmail);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void updStaff(EnStaff staff, string companyid)
        {
            string strCommandText = "updStaffTmp" + companyid;
            SqlParameter[] paraLocal = new SqlParameter[12];
            paraLocal[0] = new SqlParameter("@StaffName", staff.staffName);
            paraLocal[1] = new SqlParameter("@StaffEmail", staff.staffEmail);
            paraLocal[2] = new SqlParameter("@StaffAddress", staff.staffAddress);
            paraLocal[3] = new SqlParameter("@StaffPhone", staff.staffPhone);
            paraLocal[4] = new SqlParameter("@StaffCompanyID", staff.staffCompanyID);
            paraLocal[5] = new SqlParameter("@StaffBirthday", staff.staffBirthday); 
            paraLocal[6] = new SqlParameter("@StaffGender", staff.staffGender);
            paraLocal[7] = new SqlParameter("@StaffScale", staff.staffScale); 
            paraLocal[8] = new SqlParameter("@StaffLevel", staff.staffLevel);
            paraLocal[9] = new SqlParameter("@StaffPermissionID", staff.staffPermissionID);
            paraLocal[10] = new SqlParameter("@StaffResultUnitID", staff.staffResultUnitID);
            paraLocal[11] = new SqlParameter("@StaffPosition", staff.staffPosition);

            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void updMyProfile(EnStaff staff, string companyid)
        {
            string strCommandText = "updMyProfileTmp" + companyid;
            SqlParameter[] paraLocal = new SqlParameter[11];
            paraLocal[0] = new SqlParameter("@StaffName", staff.staffName);
            paraLocal[1] = new SqlParameter("@StaffEmail", staff.staffEmail);
            paraLocal[2] = new SqlParameter("@StaffAddress", staff.staffAddress);
            paraLocal[3] = new SqlParameter("@StaffPhone", staff.staffPhone);
            paraLocal[4] = new SqlParameter("@StaffCompanyID", staff.staffCompanyID);
            paraLocal[5] = new SqlParameter("@StaffBirthday", staff.staffBirthday);
            paraLocal[6] = new SqlParameter("@StaffGender", staff.staffGender);
            paraLocal[7] = new SqlParameter("@StaffScale", staff.staffScale);
            paraLocal[8] = new SqlParameter("@StaffLevel", staff.staffLevel);
            paraLocal[9] = new SqlParameter("@StaffResultUnitID", staff.staffResultUnitID);
            paraLocal[10] = new SqlParameter("@StaffPosition", staff.staffPosition);

            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void insStaff(EnStaff staff, string companyid)
        {
            string strCommandText = "insStaffTmp" + companyid;
            SqlParameter[] paraLocal = new SqlParameter[13];
            paraLocal[0] = new SqlParameter("@StaffName", staff.staffName);
            paraLocal[1] = new SqlParameter("@StaffEmail", staff.staffEmail);
            paraLocal[2] = new SqlParameter("@StaffAddress", staff.staffAddress);
            paraLocal[3] = new SqlParameter("@StaffPhone", staff.staffPhone);
            paraLocal[4] = new SqlParameter("@StaffCompanyID", staff.staffCompanyID);
            paraLocal[5] = new SqlParameter("@StaffBirthday", staff.staffBirthday);
            paraLocal[6] = new SqlParameter("@StaffGender", staff.staffGender);
            paraLocal[7] = new SqlParameter("@StaffScale", staff.staffScale);
            paraLocal[8] = new SqlParameter("@StaffLevel", staff.staffLevel);
            paraLocal[9] = new SqlParameter("@StaffPermissionID", staff.staffPermissionID);
            paraLocal[10] = new SqlParameter("@StaffResultUnitID", staff.staffResultUnitID);
            paraLocal[11] = new SqlParameter("@StaffPassword", staff.staffPassword);
            paraLocal[12] = new SqlParameter("@StaffPosition", staff.staffPosition);

            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void insStaffOpt(EnStaff staff, string companyid)
        {
            string strCommandText = "insStaffOpt" + companyid;
            SqlParameter[] paraLocal = new SqlParameter[7];
            paraLocal[0] = new SqlParameter("@StaffName", staff.staffName);
            paraLocal[1] = new SqlParameter("@StaffEmail", staff.staffEmail);
            paraLocal[2] = new SqlParameter("@StaffAddress", staff.staffAddress);
            paraLocal[3] = new SqlParameter("@StaffDepartmentID", staff.staffDepartmentID);
            paraLocal[4] = new SqlParameter("@StaffPermissionID", staff.staffPermissionID);
            paraLocal[5] = new SqlParameter("@StaffPassword", staff.staffPassword);
            paraLocal[6] = new SqlParameter("@StaffCompanyID", staff.staffCompanyID);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void delSAStaff(EnStaff staff, string companyid)
        {
            string strCommandText = "delSAStaff" + companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@StaffEmail", staff.staffEmail);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void delSPStaff(EnStaff staff, string companyid)
        {
            string strCommandText = "delSPStaff" + companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@StaffEmail", staff.staffEmail);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public void delStaff(EnStaff staff, string companyid)
        {
            string strCommandText = "delStaff" + companyid;
            SqlParameter[] paraLocal = new SqlParameter[1];
            paraLocal[0] = new SqlParameter("@StaffEmail", staff.staffEmail);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }

        public int UpdatePass(EnStaff staff, string passNew, string companyid)
        {
            string strCommandText = "updPassword" + companyid;
            SqlParameter[] paraLocal = new SqlParameter[4];
            paraLocal[0] = new SqlParameter("@Email", staff.staffEmail);
            paraLocal[1] = new SqlParameter("@PasswordOld", UtiGeneralClass.Md5Encode(staff.staffPassword));
            paraLocal[2] = new SqlParameter("@PasswordNew", UtiGeneralClass.Md5Encode(passNew));
            paraLocal[3] = new SqlParameter("@Confirm", SqlDbType.SmallInt);
            paraLocal[3].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
            int intResult = int.Parse(paraLocal[3].Value.ToString());

            return intResult;
        }

        public void updLostPassword(EnStaff staff, string companyid)
        {
            string strCommandText = "updLostPassword"+companyid;
            SqlParameter[] paraLocal = new SqlParameter[2];
            paraLocal[0] = new SqlParameter("@Email", staff.staffEmail);
            paraLocal[1] = new SqlParameter("@Password", UtiGeneralClass.Md5Encode(staff.staffPassword));

            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, strCommandText, paraLocal);
        }
    }
}
